import openpyxl
import re


# 读取 Excel 文件
file_path = r"D:\WorkCode\automation_api\others\清洗后的数据.xlsx"
wbook = openpyxl.load_workbook(file_path)
sheet = wbook.active
# 找到 analysis_result 和 label 列的索引
headers = [cell.value for cell in sheet[1]]
analysis_col_idx = headers.index("analysis_result") + 1
label_col_idx = headers.index("label") + 1

# 添加新列表头
sheet.cell(row=1, column=5, value="命中的标签")
sheet.cell(row=1, column=6, value="匹配成功的标签")
sheet.cell(row=1, column=7, value="成功率")

SPECIAL_KEYS = {"进行的生长发育相关检查": ["本次就诊检查", "历史检查"]}


def normalize_text(text):
    """规范化文本，处理特殊字符"""
    if text:
        text = text.replace("：", ":")  # 统一全角和半角冒号
        text = re.sub(r"\s*:\s*", ":", text)  # 去除冒号两侧空格
    return text


def parse_to_dict(text):
    """解析特定格式的文本为字典"""
    result = {}
    if text:
        text = normalize_text(text)
        lines = text.split("\n")
        for line in lines:
            if ":" in line:
                key, value = line.split(":", 1)
                result[key.strip()] = value.strip()
    return result


def is_subset(value_a, value_b):
    """检查 value_a 是否是 value_b 的子集（按逗号分隔）"""
    set_a = set(value_a.split(","))
    set_b = set(value_b.split(","))
    return set_a.issubset(set_b)


# 遍历 Excel 行，从第二行开始
for row in range(2, sheet.max_row + 1):
    analysis_text = sheet.cell(row=row, column=analysis_col_idx).value
    label_text = sheet.cell(row=row, column=label_col_idx).value

    analysis_dict = parse_to_dict(analysis_text)
    label_dict = parse_to_dict(label_text)

    matched_keys = list(analysis_dict.keys())  # 先将所有键加入命中的标签
    matched_pairs = []

    for key, value in analysis_dict.items():
        if key in label_dict:
            if is_subset(value, label_dict[key]):
                matched_pairs.append(f"{key}:{value}")
        elif key in SPECIAL_KEYS:
            for special_key in SPECIAL_KEYS[key]:
                if special_key in label_dict and is_subset(value, label_dict[special_key]):
                    matched_pairs.append(f"{key}:{value}")
                    break  # 确保只匹配一次

    # 计算成功率（命中的标签不为空才计算，否则为空）
    total_matched = len(matched_keys)
    successful_matches = len(matched_pairs)
    success_rate = round(successful_matches / total_matched, 2) if total_matched > 0 else ""

    # 写入匹配成功的键和键值对
    sheet.cell(row=row, column=5, value="\n".join(matched_keys))
    sheet.cell(row=row, column=6, value="\n".join(matched_pairs))
    sheet.cell(row=row, column=7, value=success_rate)

# 保存新的 Excel 文件
output_file = "清洗后的数据_处理后.xlsx"
wbook.save(output_file)
print(f"数据清洗完成，新的 Excel 文件已保存: {output_file}")
